﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;

public partial class ThanhToanCongNoThu : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmTTCongNoThu";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static System.Data.DataTable dt;
    static int PageNumber = 5;
    static int CurrentPage = 1;
    static DataTable dtPrint;
    int pc;
    int loaicongno;
    double sotien;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {

                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                System.Data.DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    Title = "Thiết lập công nợ thu | " + Header;
                    btnCancel_Click(sender, e);

                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year2; i >= year1; i--)
                    {
                        dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                        drnam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                    }
                    for (int i = 1; i <= 12; i++)
                    {
                        drthang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
                    }
                    dlNam.SelectedValue = year2.ToString();
                    drnam.SelectedValue = year2.ToString();


                    drthang.Items.Insert(0, new ListItem("Theo thời gian", ""));
                    drthang.SelectedIndex = 0;
                    drnam.SelectedValue = year2.ToString();

                    txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
                    drnam.Visible = false;
                    LoadKHTimKiem();
                    LoadKhachHang();
                    LoadLoaiKhachHang();
                    LoadTK();
                    LoadNVKD();
                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    void LoadKhachHang()
    {
        drkhachhang.Items.Clear();
        drkhachhang.DataSource = ex.GetData("sp_ThanhToanCongNoThu_KhachHang");
        drkhachhang.DataBind();
        drkhachhang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        drkhachhang.SelectedIndex = 0;
    }

    void LoadKhachHangAll()
    {
        drkhachhang.Items.Clear();
        drkhachhang.DataSource = ex.GetData("sp_ThanhToanCongNoThu_ListKHAll");
        drkhachhang.DataBind();
        drkhachhang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        drkhachhang.SelectedIndex = 0;
    }

    void LoadNVKD()
    {

        drNVKD.Items.Insert(0, new ListItem("--Chọn NVKD--", ""));
        drNVKD.SelectedIndex = 0;
    }

    protected void LoadNVKDTheoKH()
    {
        Guid id = Guid.NewGuid();

        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDs", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        drNVKD.Items.Clear();
        drNVKD.DataSource = ex.GetData("sp_ThanhToanCongNoThu_NVKD_TheoKH", p);
        drNVKD.DataBind();
        drNVKD.Items.Insert(0, new ListItem("--Chọn NVKD--", ""));
        drNVKD.Items.Insert(1, new ListItem("--Của công ty--", id.ToString()));
        drNVKD.SelectedIndex = 0;
    }

    void LoadLoaiKhachHang()
    {
        drloaikhachhang.Items.Clear();
        drloaikhachhang.DataSource = ex.GetData("sp_ThanhToanCongNoThu_LoaiKhachHang");
        drloaikhachhang.DataBind();
        drloaikhachhang.Items.Insert(0, new ListItem("--Chọn loại khách hàng--", ""));
        drloaikhachhang.SelectedIndex = 0;
    }
    protected void drloaikhachhang_SelectedIndexChanged(object sender, EventArgs e)
    {
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDLoaiKhachHang", ex.GetGuid(drloaikhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        drkhachhang.Items.Clear();
        drkhachhang.DataSource = ex.GetData("sp_ThanhToanCongNoThu_KhachHang_TheoLoaiKhachHang", p);
        drkhachhang.DataBind();
        drkhachhang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        drkhachhang.SelectedIndex = 0;
    }
    protected void drkhachhang_SelectedIndexChanged(object sender, EventArgs e)
    {
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienHoTroCuoc", p);
        if (dt.Rows[0]["hotrocuoc"].ToString().Equals(""))
            txthotrocuocvat.Text = "0";
        else
            txthotrocuocvat.Text = ConvertDecimal(double.Parse(dt.Rows[0]["hotrocuoc"].ToString()));

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienBeTong_VAT", p);
        if (dt.Rows[0]["tienbetong"].ToString().Equals(""))
            txttienbetongVAT.Text = "0";
        else
            txttienbetongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["tienbetong"].ToString()));

        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienBeTong_KhongVAT", p);
        if (dt.Rows[0]["tienbetong"].ToString().Equals(""))
            txttienbetongkhongVAT.Text = "0";
        else
            txttienbetongkhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["tienbetong"].ToString()));

        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienCaBom_VAT", p);
        if (dt.Rows[0]["tiencabom"].ToString().Equals(""))
            txttienbomVAT.Text = "0";
        else
            txttienbomVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["tiencabom"].ToString()));

        txtthanhtienhangVAT.Text = ConvertDecimal(double.Parse(txttienbomVAT.Text) + double.Parse(txttienbetongVAT.Text) + double.Parse(txthotrocuocvat.Text));
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienCaBom_KhongVAT", p);
        if (dt.Rows[0]["tiencabom"].ToString().Equals(""))
            txttienbomkhongVAT.Text = "0";
        else
            txttienbomkhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["tiencabom"].ToString()));


        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_TienHoTroCuoc_KhongVAT", p);
        if (dt.Rows[0]["hotrocuoc"].ToString().Equals(""))
            txttienhotrocuockhongvat.Text = "0";
        else
            txttienhotrocuockhongvat.Text = ConvertDecimal(double.Parse(dt.Rows[0]["hotrocuoc"].ToString()));
        txtthanhtienkhongVAT.Text = ConvertDecimal(double.Parse(txttienbetongkhongVAT.Text) + double.Parse(txttienbomkhongVAT.Text) + double.Parse(txttienhotrocuockhongvat.Text));

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_SoDu_VAT_KiTruoc", p);
        if (dt.Rows[0]["sodu"].ToString().Equals(""))
            txtsodukitruocVAT.Text = "0";
        else
            txtsodukitruocVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sodu"].ToString()));

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_SoDu_KhongVAT_KiTruoc", p);
        if (dt.Rows[0]["sodu"].ToString().Equals(""))
            txtsodukitruockhongVAT.Text = "0";
        else
            txtsodukitruockhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sodu"].ToString()));

        txtsodukytruoc.Text = ConvertDecimal(double.Parse(txtsodukitruockhongVAT.Text) + double.Parse(txtsodukitruocVAT.Text));


        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_ThanhToan_VAT_KiTruoc", p);
        if (dt.Rows[0]["sott"].ToString().Equals(""))
            txtsothanhtoankitruocVAT.Text = "0";
        else
            txtsothanhtoankitruocVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sott"].ToString()));
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_ThanhToan_KhongVAT_KiTruoc", p);
        if (dt.Rows[0]["sott"].ToString().Equals(""))
            txtsothanhtoankitruockhongVAT.Text = "0";
        else
            txtsothanhtoankitruockhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sott"].ToString()));
        txttongthanhtoankytruoc.Text = ConvertDecimal(double.Parse(txtsothanhtoankitruockhongVAT.Text) + double.Parse(txtsothanhtoankitruocVAT.Text));
        txttongphaithuVAT.Text = ConvertDecimal(double.Parse(txtsodukitruocVAT.Text) + double.Parse(txtthanhtienhangVAT.Text));
        txttongphaithukhongVAT.Text = ConvertDecimal(double.Parse(txtsodukitruockhongVAT.Text) + double.Parse(txtthanhtienkhongVAT.Text));

        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_ThanhToan_VAT_KiNay", p);
        if (dt.Rows[0]["sotien"].ToString().Equals(""))
            txtsotienthanhtoankynayVAT.Text = "0";
        else
            txtsotienthanhtoankynayVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotien"].ToString()));

        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToanCongNoThu_ThanhToan_KhongVAT_KiNay", p);
        if (dt.Rows[0]["sotien"].ToString().Equals(""))
            txtsotienthanhtoankynaykhongVAT.Text = "0";
        else
            txtsotienthanhtoankynaykhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotien"].ToString()));

        txttongthanhtoankynay.Text = ConvertDecimal(double.Parse(txtsotienthanhtoankynaykhongVAT.Text) + double.Parse(txtsotienthanhtoankynayVAT.Text));
        txtsoduconlaiVAT.Text = ConvertDecimal(double.Parse(txttongphaithuVAT.Text) - double.Parse(txtsotienthanhtoankynayVAT.Text));
        txtsoduconlaikhongVAT.Text = ConvertDecimal(double.Parse(txttongphaithukhongVAT.Text) - double.Parse(txtsotienthanhtoankynaykhongVAT.Text));
        txttongduconlai.Text = ConvertDecimal(double.Parse(txtsoduconlaiVAT.Text) + double.Parse(txtsoduconlaikhongVAT.Text));
        tienhangVAT.Text = ConvertDecimal(double.Parse(txtsodukitruocVAT.Text) + double.Parse(txtthanhtienhangVAT.Text) + double.Parse(txtsothanhtoankitruocVAT.Text));
        tienhangkhongVAT.Text = ConvertDecimal(double.Parse(txtsodukitruockhongVAT.Text) + double.Parse(txtthanhtienkhongVAT.Text) + double.Parse(txtsothanhtoankitruockhongVAT.Text));
        txtthanhtienhang.Text = ConvertDecimal(double.Parse(tienhangkhongVAT.Text) + double.Parse(tienhangVAT.Text));
        LoadNVKDTheoKH();
    }


    void LoadTK()
    {
        drtaikhoan.Items.Clear();
        drtaikhoan.DataSource = ex.GetData("sp_ListTaiKhoan_KhongVAT");
        drtaikhoan.DataBind();
        drtaikhoan.Items.Insert(0, new ListItem("--Chọn tài khoản chuyển tiền--", ""));
        drtaikhoan.SelectedIndex = 0;
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drkhachhang.SelectedIndex == 0)
        {
            LoadKhachHang();
        }
        else
            drkhachhang_SelectedIndexChanged(sender, e);
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (f_Them.Equals(1))
        {

            try
            {
                if (drhinhthucthanhtoan.SelectedIndex == 1)
                {
                    loaicongno = 1;
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    dt = ex.GetData("sp_xdagetsophieuchiconnothuvat_NganHang", p);
                    int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                    if (sopc == 0)
                    {
                        pc = 1;

                    }
                    else pc = sopc + 1;
                }
                else if (drhinhthucthanhtoan.SelectedIndex == 2)
                {
                    loaicongno = 2;
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    dt = ex.GetData("sp_xdagetsophieuthuconnotravat_TienMat", p);
                    int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                    if (sopc == 0)
                        pc = 1;
                    else pc = sopc + 1;
                }
                //else
                //{
                //    loaicongno = DBNull.Value;
                //    int sopc = DBNull.Value;
                //}
                Guid idtt = Guid.NewGuid();
                p = new SqlParameter[20];
                p[0] = new SqlParameter("@IDs", idtt);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@KhachHang", ex.GetGuid(drkhachhang.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@SoDuKiTruocVAT", double.Parse(txtsodukitruocVAT.Text.Trim().Replace(",", "")));
                p[2].SqlDbType = SqlDbType.Float;
                p[3] = new SqlParameter("@TienBomVAT", double.Parse(txttienbomVAT.Text.Trim().Replace(",", "")));
                p[3].SqlDbType = SqlDbType.Float;
                p[4] = new SqlParameter("@TienBeTongVAT", double.Parse(txttienbetongVAT.Text.Trim().Replace(",", "")));
                p[4].SqlDbType = SqlDbType.Float;
                p[5] = new SqlParameter("@TienHoTroCuocVAT", double.Parse(txthotrocuocvat.Text.Trim().Replace(",", "")));
                p[5].SqlDbType = SqlDbType.Float;
                p[6] = new SqlParameter("@SoDuKiTruocKhongVAT", double.Parse(txtsodukitruockhongVAT.Text.Trim().Replace(",", "")));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@TienBomKhongVAT", double.Parse(txttienbomkhongVAT.Text.Trim().Replace(",", "")));
                p[7].SqlDbType = SqlDbType.Float;
                p[8] = new SqlParameter("@TienBeTongKhongVAT", double.Parse(txttienbetongkhongVAT.Text.Trim().Replace(",", "")));
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@TienHoTroCuocKhongVAT", double.Parse(txttienhotrocuockhongvat.Text.Trim().Replace(",", "")));
                p[9].SqlDbType = SqlDbType.Float;
                string txt = txtttkinayvat.Text.Trim().Replace(",", "").Equals("") ? "0" : txtttkinayvat.Text.Trim().Replace(",", "");
                if (double.Parse(txt).Equals(0))
                {
                    p[10] = new SqlParameter("@SoTienTTVAT", double.Parse("0"));
                    p[10].SqlDbType = SqlDbType.Float;
                    p[11] = new SqlParameter("@SoTienTTKhongVAT", double.Parse(txtttkinaykhongvat.Text.Trim().Replace(",", "")));
                    p[11].SqlDbType = SqlDbType.Float;
                    p[12] = new SqlParameter("@HinhThucVAT", drhinhthucthanhtoan.SelectedValue);
                    p[12].SqlDbType = SqlDbType.Int;
                    p[13] = new SqlParameter("@HinhThucKhongVAT", DBNull.Value);
                    p[13].SqlDbType = SqlDbType.Int;
                    if (drhinhthucthanhtoan.SelectedIndex == 1)
                    {
                        p[14] = new SqlParameter("@TaiKhoanVAT", ex.GetGuid(drtaikhoan.SelectedValue));
                        p[14].SqlDbType = SqlDbType.UniqueIdentifier;
                    }
                    else
                    {
                        p[14] = new SqlParameter("@TaiKhoanVAT", DBNull.Value);
                        p[14].SqlDbType = SqlDbType.UniqueIdentifier;
                    }
                    p[15] = new SqlParameter("@TaiKhoanKhongVAT", DBNull.Value);
                    p[15].SqlDbType = SqlDbType.UniqueIdentifier;
                }

                else
                {
                    p[10] = new SqlParameter("@SoTienTTVAT", double.Parse(txtttkinayvat.Text.Trim().Replace(",", "")));
                    p[10].SqlDbType = SqlDbType.Float;
                    p[11] = new SqlParameter("@SoTienTTKhongVAT", DBNull.Value);
                    p[11].SqlDbType = SqlDbType.Float;
                    p[12] = new SqlParameter("@HinhThucVAT", drhinhthucthanhtoan.SelectedValue);
                    p[12].SqlDbType = SqlDbType.Int;
                    p[13] = new SqlParameter("@HinhThucKhongVAT", DBNull.Value);
                    p[13].SqlDbType = SqlDbType.Int;
                    if (drhinhthucthanhtoan.SelectedIndex == 1)
                    {
                        p[14] = new SqlParameter("@TaiKhoanVAT", ex.GetGuid(drtaikhoan.SelectedValue));
                        p[14].SqlDbType = SqlDbType.UniqueIdentifier;
                    }
                    else
                    {
                        p[14] = new SqlParameter("@TaiKhoanVAT", DBNull.Value);
                        p[14].SqlDbType = SqlDbType.UniqueIdentifier;
                    }
                    p[15] = new SqlParameter("@TaiKhoanKhongVAT", DBNull.Value);
                    p[15].SqlDbType = SqlDbType.UniqueIdentifier;
                }
                p[16] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                p[16].SqlDbType = SqlDbType.SmallDateTime;
                p[17] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
                p[17].SqlDbType = SqlDbType.NVarChar;
                p[18] = new SqlParameter("@Del", 1);
                p[18].SqlDbType = SqlDbType.Int;
                p[19] = new SqlParameter("@SoPT", pc);
                p[19].SqlDbType = SqlDbType.Int;
                ex.ExecuteNonQuerry("sp_xdathanhtoancongnothu_Add", p);

                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
                p[2].SqlDbType = SqlDbType.NVarChar;
                p[3] = new SqlParameter("@DonVi", ex.GetGuid(drkhachhang.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@SoPC", pc);
                p[4].SqlDbType = SqlDbType.Int;
                if (drhinhthucthanhtoan.SelectedIndex == 1)
                {
                    p[5] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                    p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                }
                else
                {
                    p[5] = new SqlParameter("@TaiKhoan", DBNull.Value);
                    p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                }

                p[6] = new SqlParameter("@Del", 1);
                p[6].SqlDbType = SqlDbType.Int;
                p[7] = new SqlParameter("@LoaiCongNo", loaicongno);
                p[7].SqlDbType = SqlDbType.Int;

                if (double.Parse(txt).Equals(0))
                {

                    sotien = double.Parse(txtttkinaykhongvat.Text.Trim().Replace(",", ""));
                }
                else
                {

                    sotien = double.Parse(txtttkinayvat.Text.Trim().Replace(",", ""));
                }
                p[8] = new SqlParameter("@SoTien", sotien);
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@IDLenhChi", idtt);
                p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                p[10] = new SqlParameter("@NhanVien", ex.GetGuid(drNVKD.SelectedValue));
                p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                ex.ExecuteNonQuerry("sp_XDAPhieuChiCongNoThu_Add", p);




                gstGetMess("Lưu thành công", "");

                btnCancel_Click(sender, e);


            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tthanh toán công nợ trả\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    }


    protected void btnCancel_Click(object sender, EventArgs e)
    {

        drkhachhang.SelectedIndex = 0;
        drloaikhachhang.SelectedIndex = 0;
        txtttkinaykhongvat.Text = "0";
        txtttkinayvat.Text = "0";
        btncapnhat.Visible = false;
        btnSave.Visible = true;
        txtngaythanhtoan.Text = "";
        drhinhthucthanhtoan.SelectedIndex = 0;
        drtaikhoan.SelectedIndex = 0;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;
        GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List", p);
        GV.DataBind();
        dt = ex.GetData("sp_xdathanhtoancongnothu_List_Footer");
        dlThang.SelectedIndex = 0;
        dlNam.SelectedIndex = 0;
        txtnoidung.Text = "";
        LoadFooter();

        txthotrocuocvat.Text = "";
        txttienbetongVAT.Text = "";
        txttienbetongkhongVAT.Text = "";
        txttienbomVAT.Text = "";
        txtthanhtienhangVAT.Text = "";
        txttienbomkhongVAT.Text = "";
        txttienhotrocuockhongvat.Text = "";
        txtthanhtienkhongVAT.Text = "";
        txtsodukitruocVAT.Text = "";
        txtsodukytruoc.Text = "";
        txtsothanhtoankitruocVAT.Text = "";
        txtsothanhtoankitruockhongVAT.Text = "";
        txttongthanhtoankytruoc.Text = "";
        txttongphaithuVAT.Text = "";
        txttongphaithukhongVAT.Text = "";
        txtsotienthanhtoankynayVAT.Text = "";
        txtsotienthanhtoankynaykhongVAT.Text = "";
        txttongthanhtoankynay.Text = "";
        txtsoduconlaiVAT.Text = "";
        txtsoduconlaikhongVAT.Text = "";
        txttongduconlai.Text = "";
        tienhangVAT.Text = "";
        tienhangkhongVAT.Text = "";
        txtthanhtienhang.Text = "";
        drhinhthucthanhtoan.Enabled = true;
        drkhachhang.Enabled = true;
        drloaikhachhang.Enabled = true;
    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        hdID.Value = ID;
        Guid gID = new Guid(ID);
        if (e.CommandName.Equals("Del"))
        {

            if (f_Xoa.Equals(1))
            {
                try
                {

                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_ThanhToanCongNoThu_DelPhieuChiCongNoThu", p);

                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("ThanhToanCongNoThu_DelPhieuThu", p);

                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    ex.ExecuteNonQuerry("sp_ThanhToanCongNoThu_Del", p);
                    btnCancel_Click(sender, e);
                    gstGetMess("Xóa thành công", "");
                }
                catch (Exception ax)
                {
                    gstGetMess("Có lỗi trong quá trình xóa, xem trong file log", "");
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThanh toán công nợ\t" + ax.Message);

                }
            }
            else
                gstGetMess("Bạn không có quyền xóa dữ liệu trên trang này", "");
        }
        else
        {
            LoadKhachHangAll();
            p = new SqlParameter[1];
            p[0] = new SqlParameter("@IDs", gID);
            p[0].SqlDbType = SqlDbType.UniqueIdentifier;
            DataTable dt = ex.GetData("sp_ThanhToanCongNoThu_ListByID", p);
            if (dt.Rows.Count > 0)
            {
                try
                {
                    dlThang.SelectedValue = dt.Rows[0]["thang"].ToString();
                    txtngaythanhtoan.Text = dt.Rows[0]["ngaythang"].ToString();
                    drkhachhang.SelectedValue = dt.Rows[0]["khachhang"].ToString();
                    drkhachhang_SelectedIndexChanged(sender, e);
                    txtttkinayvat.Text = dt.Rows[0]["sotienttvat"].ToString();
                    txtttkinaykhongvat.Text = dt.Rows[0]["sotienttkhongvat"].ToString();
                    txtnoidung.Text = dt.Rows[0]["noidung"].ToString();
                    drhinhthucthanhtoan.SelectedValue = dt.Rows[0]["hinhthucvat"].ToString();
                    drtaikhoan.SelectedValue = dt.Rows[0]["taikhoanvat"].ToString();
                    drNVKD.SelectedValue = dt.Rows[0]["nhanvien"].ToString();
                    drhinhthucthanhtoan.Enabled = false;
                    drkhachhang.Enabled = false;
                    drloaikhachhang.Enabled = false;

                }
                catch (Exception ax)
                {
                    gstGetMess("Có lỗi trong quá trình xem, xem trong file log", "");
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tCông nợ thu\t" + ax.Message);
                }

            }
            btncapnhat.Visible = true;
            btnSave.Visible = false;
        }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List", p);
        GV.DataBind();



        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List", p);
            GV.DataBind();



        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List", p);
        GV.DataBind();


        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }

    private void LoadFooter()
    {
        string[] field = { "SoTienTTVAT", "SoTienTTKhongVAT" };
        if (dt.Rows.Count > 0)
        {
            GV.FooterRow.Cells[2].Text = "Tổng cộng";
            GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            for (int k = 4; k < 6; k++)
            {
                GV.FooterRow.Cells[k].Text = ConvertDecimal(double.Parse(dt.Compute("sum(" + field[k - 4] + ")", "").ToString()));
            }


        }
    }
    protected void drthang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drthang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            drnam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            drnam.Visible = true;
        }
        LoadKHTimKiem();
    }

    void LoadKHTimKiem()
    {
        getDate();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        dlkhachhang.Items.Clear();
        dlkhachhang.DataSource = ex.GetData("sp_ThanhToanCongNoThu_ListKhachHang", p);
        dlkhachhang.DataBind();
        dlkhachhang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dlkhachhang.SelectedIndex = 0;
    }

    public void getDate()
    {
        if (drthang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + drthang.SelectedValue + "/" + drnam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(drthang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(drnam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        getDate();
        if (dlkhachhang.SelectedIndex == 0 && dlhinhthucthanhtoan.SelectedIndex == 0)
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian", p);
            GV.DataBind();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            dt = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian", p);
            LoadFooter();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            dtPrint = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_Print", p);
        }
        else if (dlkhachhang.SelectedIndex > 0 && dlhinhthucthanhtoan.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[3].SqlDbType = SqlDbType.Int;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang_HinhThuc", p);
            GV.DataBind();
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[3].SqlDbType = SqlDbType.Int;
            dt = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang_HinhThuc", p);
            LoadFooter();

            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[3].SqlDbType = SqlDbType.Int;
            dtPrint = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang_HinhThuc_Print", p);
        }
        else if (dlhinhthucthanhtoan.SelectedIndex == 0 && dlkhachhang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang", p);
            GV.DataBind();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            dt = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang", p);


            LoadFooter();

            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlkhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            dtPrint = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_KhachHang_Print", p);
        }

        else if (dlhinhthucthanhtoan.SelectedIndex > 0 && dlkhachhang.SelectedIndex == 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[2].SqlDbType = SqlDbType.Int;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_HinhThuc", p);
            GV.DataBind();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[2].SqlDbType = SqlDbType.Int;
            dt = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_HinhThuc", p);
            LoadFooter();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@HinhThuc", dlhinhthucthanhtoan.SelectedValue);
            p[2].SqlDbType = SqlDbType.Int;
            dtPrint = ex.GetData("sp_xdathanhtoancongnothu_List_TheoThoiGian_HinhThuc_Print", p);
        }

        btnFirst.Visible = false;
        btnNext.Visible = false;
        btnPre.Visible = false;
    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {

            PrintNPOI(dtPrint);
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }

    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Danh sách";




            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text + " " + dlNam.SelectedItem.Text : "Từ " + txtTuNgay.Text + " Đến " + txtDenNgay.Text;
            //  string congtrinh = dlCongTrinh.SelectedIndex > 0 ? " - " + dlCongTrinh.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Danh sách thanh toán công nợ thu " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //khachhang
            //rowIndex++;
            //row = sheet.CreateRow(rowIndex);
            //r1c1 = row.CreateCell(0);
            //r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text + congtrinh : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            //r1c1.CellStyle = hs3;
            //r1c1.Row.Height = 500;



            //freeze panes

            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày tháng", "Khách hàng", "Số tiền TT có VAT", "Số tiền TT không VAT", "Hình thức TT", "Tài khoản TT", "Nội dung"
                              };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;

                sheet.AutoSizeColumn(j);

            }

            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 3 || j == 4)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 1)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }


            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "D", "E" };
            for (int k = 3; k <= 4; k++)
            {

                cell = row.CreateCell(k);
                cell.CellFormula = "Sum(" + footer[k - 3] + "6:" + footer[k - 3] + rowIndex.ToString() + ")";
                cell.Row.Height = 500;
                cell.CellStyle = hs4;

            }
            //fix lai border
            r1c1 = row.CreateCell(0);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;

            r1c1 = row.CreateCell(1);
            r1c1.CellStyle = hs4;
            r1c1.Row.Height = 400;
            for (int k = 5; k <= 7; k++)
            {

                cell = row.CreateCell(k);

                cell.Row.Height = 500;
                cell.CellStyle = hs4;

            }


            for (int ik = 0; ik <= 7; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }


            //end sheet tong hop--------------------------
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);

                string saveAsFileName = "DSTTCongNoThu-" + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void btncapnhat_Click(object sender, EventArgs e)
    {
         if (f_Sua.Equals(1))
        {
        Guid gID = new Guid(hdID.Value);
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@IDs", gID);
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@SoTienTTVAT", double.Parse(txtttkinayvat.Text.Trim().Replace(",", "").Equals("") ? "0" : txtttkinayvat.Text.Trim().Replace(",", "")));
        p[1].SqlDbType = SqlDbType.Float;
        p[2] = new SqlParameter("@SoTienTTKhongVAT",double.Parse(txtttkinaykhongvat.Text.Trim().Replace(",", "").Equals("") ? "0" : txtttkinaykhongvat.Text.Trim().Replace(",", "")));
        p[2].SqlDbType = SqlDbType.Float;
        p[3] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
        p[4].SqlDbType = SqlDbType.SmallDateTime;
        p[5] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
        p[5].SqlDbType = SqlDbType.NVarChar;
        ex.ExecuteNonQuerry("sp_ThanhToanCongNoThu_Update", p);


        p = new SqlParameter[6];

        p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
        p[1].SqlDbType = SqlDbType.NVarChar;
        p[2] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        string txt = txtttkinayvat.Text.Trim().Replace(",", "").Equals("") ? "0" : txtttkinayvat.Text.Trim().Replace(",", "");
        if (double.Parse(txt).Equals(0))
        {

            sotien = double.Parse(txtttkinaykhongvat.Text.Trim().Replace(",", ""));
        }
        else
        {

            sotien = double.Parse(txtttkinayvat.Text.Trim().Replace(",", ""));
        }
        p[3] = new SqlParameter("@SoTien", sotien);
        p[3].SqlDbType = SqlDbType.Float;
        p[4] = new SqlParameter("@IDLenhChi", gID);
        p[4].SqlDbType = SqlDbType.UniqueIdentifier;
        p[5] = new SqlParameter("@NhanVien", ex.GetGuid(drNVKD.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
        ex.ExecuteNonQuerry("sp_ThanhToanCongNoThu_UpdatePhieuChiCongNoThu", p);
        gstGetMess("Cập nhật thành công","");
        btnCancel_Click(sender,e);
        }
         else
             gstGetMess("Bạn không có quyền sửa dữ liệu trên trang này", "");
    }
}